﻿' ------------------------------------------------------------------------------
' The SqlFilestream provides two methods, LoadBytes() and SaveBytes(),
' for accessing data stored in SQL Server filestream columns. More specifically,
' these methods work on columns declared as 
'
'    VARBINARY(MAX) FILESTREAM
'
' Each method requires 
'
' - guid key for the row to query (filestream columns must have a unique
'   key declared as ROWGUID)
' - table name
' - column name
' - connection
'
' The varbinary data is passed in or returned as a Byte array.
'
' Per the documentation for SQL Server 2008 CTP6, a handle to the
' filestream storage on disk is obtained by calling the
' OpenSqlFilestream function in the SQL Server dll named
' "sqlncli10.dll".
'
' During development, intermittent problems occured under low memory
' conditions when SafeFileHandle objects become invalid at random. We
' never became clear on the precise nature of the problem, but it
' appeared that the errors were occuring during garbage collection. One
' possibility is that the handles were not being properly
' closed/disposed by this code. Several changes to the handle method
' calls eventually resulted in stable code.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Imports System.Runtime.InteropServices
Imports Microsoft.Win32.SafeHandles

Public Module SqlFilestream

    ' IOP for win32 function OpenSqlFilestream in the SQL Server 2008 library "sqlncli10.dll"

    Public Const DESIRED_ACCESS_READ As UInt32 = 0
    Public Const DESIRED_ACCESS_WRITE As UInt32 = 1
    Public Const DESIRED_ACCESS_READWRITE As UInt32 = 2

    Public Const SQL_FILESTREAM_OPEN_NO_FLAGS As UInt32 = 0
    Public Const SQL_FILESTREAM_OPEN_FLAG_ASYNC As UInt32 = 1
    Public Const SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING As UInt32 = 2
    Public Const SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH As UInt32 = 4
    Public Const SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN As UInt32 = 8
    Public Const SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS As UInt32 = 16

    <DllImport("sqlncli10.dll", SetLastError:=True, CharSet:=CharSet.Unicode)> _
    Public Function OpenSqlFilestream(ByVal FilestreamPath As String, _
                                      ByVal DesiredAccess As UInt32, _
                                      ByVal OpenOptions As UInt32, _
                                      ByVal FilestreamTransactionContext As Byte(), _
                                      ByVal FilestreamTransactionContextLength As UInt32, _
                                      ByVal AllocationSize As Int64) As SafeFileHandle
    End Function

    Public Function SaveBytes(ByVal guid As Guid, ByVal bytes() As Byte, ByVal table As String, ByVal column As String, ByVal connection As Connection) As Boolean

        ReportTrace("saving image bytes() to filestream column in table " & table & " ...")

        Dim handle As SafeFileHandle = Nothing
        Dim trans As SqlTransaction = Nothing
        Dim ostr As IO.FileStream = Nothing
        Dim result() As Byte = Nothing

        Try

            ' retrieve the (mangled) path to the filestream blob

            Dim cmd As SqlCommand = connection.CreateCommand()
            cmd.CommandText = String.Format("SELECT {0}.PathName() FROM {1} WHERE RowGuid = @RowGuid", column, table)
            cmd.Parameters.AddWithValue("@RowGuid", guid.ToString)
            ReportSqlCommand("select path to filestream", cmd)
            Dim o As Object = cmd.ExecuteScalar()

            If o.Equals(DBNull.Value) Then
                Dim msg As String = String.Format("function {0}.PathName() failed to read the path name for the {1} column in table {2}", column, column, table)
                ReportError(msg)
                Return False
            End If

            Dim mangledpath As String = DirectCast(o, String)

            ' filestream operations must occur within a transaction 

            Try
                trans = cmd.Connection.BeginTransaction
            Catch ex As Exception
                ReportException(ex)
            End Try

            cmd = connection.CreateCommand()
            cmd.Transaction = trans
            cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
            ReportSqlCommand("filestream context", cmd)
            Dim obj As Object = cmd.ExecuteScalar()
            If obj.Equals(DBNull.Value) Then
                ReportError(cmd.CommandText & " failed")
                Return False
            End If

            Dim context As Byte() = DirectCast(obj, Byte())
            Dim contextlen As UInt32 = Convert.ToUInt32(context.Length())

            ' obtain a handle for writing to the filestream

            handle = OpenSqlFilestream(mangledpath, DESIRED_ACCESS_WRITE, SQL_FILESTREAM_OPEN_NO_FLAGS, context, contextlen, 0)

            ReportSqlOperation("get file handle", "path to filestream" & vbCrLf & mangledpath)
            If handle.IsInvalid Then
                ReportOpenSqlFilestreamError(GetLastError())
                Return False
            End If

            ' write the data

            ostr = New IO.FileStream(handle, IO.FileAccess.Write)
            ostr.Seek(0, IO.SeekOrigin.Begin)
            ostr.Write(bytes, 0, bytes.Length)

            ReportTrace("... wrote " & bytes.Length & " bytes to filestream column")
            ReportSqlOperation("write to file", "wrote " & bytes.Length & " bytes to filestream")

        Catch ex As Exception
            ReportException(ex)

        Finally
            If ostr IsNot Nothing Then ostr.Close()
            If handle IsNot Nothing Then
                If Not handle.IsClosed Then handle.Close()
                'handle.Dispose()
                handle.SetHandleAsInvalid()
            End If
            Try
                If trans IsNot Nothing Then trans.Commit()
            Catch ex As Exception
                ReportException(ex)
            End Try
        End Try

        Return True
    End Function

    Public Function LoadBytes(ByVal guid As Guid, ByVal table As String, ByVal column As String, ByVal connection As Connection) As Byte()

        Dim cmd As SqlCommand = connection.CreateCommand()
        ReportTrace("reading image bytes() from filestream column in table " & table & " ...")

        Dim handle As SafeFileHandle = Nothing
        Dim trans As SqlTransaction = Nothing
        Dim istr As IO.FileStream = Nothing
        Dim result() As Byte = Nothing

        Try

            ' query the database for the (mangled) path to the filestream blob

            cmd.CommandText = String.Format("SELECT {0}.PathName() FROM {1} WHERE RowGuid = @RowGuid", column, table)
            cmd.Parameters.AddWithValue("@RowGuid", guid.ToString)
            ReportSqlCommand("select path to filestream", cmd)
            Dim o As Object = cmd.ExecuteScalar()

            If o.Equals(DBNull.Value) Then
                Dim msg As String = String.Format("function {0}.PathName() failed to read the path name for the {1} column in table {2}", column, column, table)
                ReportError(msg)
                Return Nothing
            End If

            Dim mangledpath As String = DirectCast(o, String)

            ' filestream operations must occur within a transaction 

            Try
                trans = connection.SqlConnection.BeginTransaction()
            Catch ex As Exception
                ReportException(ex)
                Return Nothing
            End Try

            cmd = connection.CreateCommand()

            cmd.Transaction = trans
            cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
            ReportSqlCommand("filestream context", cmd)
            Dim obj As Object = cmd.ExecuteScalar()
            If obj.Equals(DBNull.Value) Then
                ReportError(cmd.CommandText & " failed")
                Return Nothing
            End If

            Dim context As Byte() = DirectCast(obj, Byte())
            Dim contextlen As UInt32 = Convert.ToUInt32(context.Length())

            ' obtain a handle to read the file

            handle = OpenSqlFilestream(mangledpath, DESIRED_ACCESS_READ, SQL_FILESTREAM_OPEN_NO_FLAGS, context, contextlen, 0)
            ReportSqlOperation("get file handle", "path to filestream" & vbCrLf & mangledpath)

            If handle.IsInvalid Then
                ReportOpenSqlFilestreamError(GetLastError())
                Return Nothing
            End If

            ' read data using the file handle

            istr = New IO.FileStream(handle, IO.FileAccess.Read)
            istr.Seek(0, IO.SeekOrigin.Begin)
            Dim len As Integer = Convert.ToInt32(istr.Length)
            ReDim result(len - 1)
            Dim n As Integer = istr.Read(result, 0, len)
            ReportTrace("... read " & n & " bytes from filestream column")
            ReportSqlOperation("read from file", "read " & n & " bytes from filestream column")

        Catch ex As Exception
            ReportException(ex)

        Finally
            If istr IsNot Nothing Then istr.Close()
            If handle IsNot Nothing Then
                If Not handle.IsClosed Then handle.Close()
                'handle.Dispose()
                handle.SetHandleAsInvalid()
            End If
            Try
                trans.Commit()
            Catch ex As Exception
                ReportException(ex)
            End Try
        End Try

        Return result
    End Function

End Module
